package org.sample.jdbc.commons.jdbc;

import org.sample.jdbc.commons.bean.BeanUtils;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.sql.*;
import java.util.Date;
import java.util.*;

public class DalClient implements AutoCloseable {

    protected String driver;

    protected String url;

    protected String username;

    protected String password;

    private Connection connection;

    private boolean showLog;

    public DalClient(Scanner scanner) {
        System.out.println("Whether to use the default connection?(y/n)");
        String useDefault = scanner.next();

        if ("y".equalsIgnoreCase(useDefault)) {
            InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
            Properties prop = new Properties();
            try {
                prop.load(in);
            } catch (IOException e) {
                throw new RuntimeException(e);
            }
            this.driver = prop.getProperty("default.driver");
            this.url = prop.getProperty("default.url");
            this.username = prop.getProperty("default.username");
            this.password = prop.getProperty("default.password");

        } else {
            System.out.println("Please input driver-class:");
            this.driver = scanner.next();

            System.out.println("Please input jdbc-url:");
            this.url = scanner.next();

            System.out.println("Please input username:");
            this.username = scanner.next();

            System.out.println("Please input password:");
            this.password = scanner.next();

        }
        initial();
    }

    public DalClient(String driver, String url, String username, String password) {
        this.driver = driver;
        this.url = url;
        this.username = username;
        this.password = password;
        initial();
    }

    public DalClient(String key) {
        InputStream in = Thread.currentThread().getContextClassLoader().getResourceAsStream("jdbc.properties");
        Properties prop = new Properties();
        try {
            prop.load(in);
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
        this.driver = prop.getProperty(key + ".driver");
        this.url = prop.getProperty(key + ".url");
        this.username = prop.getProperty(key + ".username");
        this.password = prop.getProperty(key + ".password");
        close(in);
        initial();
    }

    protected void initial() {
        try {
            this.connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    protected Connection getConnection() {
        return connection;
    }

    protected String getSQL(String sql) {
        return sql;
    }

    public boolean execute(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        boolean result = statement.execute();
        log(sql, args, result);
        close(statement);
        return result;
    }

    /**
     * @return 新增后返回主键
     */
    public Integer insert(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        statement.execute();
        Integer id = null;
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next())
            id = rs.getInt(1);

        log(sql, args, id);
        close(statement);
        return id;
    }

    /**
     * @return 新增后返回影响行数
     */
    public Integer insert(String sql) throws SQLException {
        sql = getSQL(sql);
        Statement statement = getConnection().createStatement();
        int row = statement.executeUpdate(sql);
        close(statement);
        return row;
    }

    public int update(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        int row = statement.executeUpdate();
        log(sql, args, row);
        close(statement);
        return row;
    }

    public String getString(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        String result = null;
        if (rs.next())
            result = rs.getString(1);
        log(sql, args, result);
        close(rs, statement);
        return result;
    }

    public List<String> findColumn(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        List<String> result = new ArrayList<>();

        while (rs.next())
            result.add(rs.getString(1));

        log(sql, args, result);
        close(rs, statement);
        return result;
    }

    public String[] get(String sql, Object... args) throws SQLException {
        List<String[]> result = find(sql, args);
        if (result != null && !result.isEmpty())
            return result.get(0);
        return null;
    }

    public List<String[]> find(String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        List<String[]> result = new ArrayList<>();

        while (rs.next()) {
            String[] entity = new String[columnCount];
            for (int i = 0; i < columnCount; i++)
                entity[i] = rs.getString(i + 1);
            result.add(entity);
        }

        log(sql, args, result);
        close(rs, statement);
        return result;
    }

    public <T> List<T> find(Class<T> clazz, String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql);
        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        List<T> result = new ArrayList<>();

        while (rs.next()) {
            T entity = BeanUtils.newInstance(clazz);

            for (int i = 1; i <= columnCount; i++) {
                String name = metaData.getColumnLabel(i);
                Method writeMethod = BeanUtils.getWriteMethod(clazz, name);
                Class<?> target = writeMethod.getParameterTypes()[0];
                BeanUtils.setValue(entity, writeMethod, get(target, rs, name));
            }
            result.add(entity);
        }

        log(sql, args, result);
        close(rs, statement);
        return result;
    }

    private static Object get(Class target, ResultSet rs, String name) throws SQLException {
        if (target.isAssignableFrom(Boolean.class) || target.isAssignableFrom(Boolean.TYPE))
            return rs.getBoolean(name);
        if (target.isAssignableFrom(Byte.class) || target.isAssignableFrom(Byte.TYPE))
            return rs.getByte(name);
        if (target.isAssignableFrom(Short.class) || target.isAssignableFrom(Short.TYPE))
            return rs.getShort(name);
        if (target.isAssignableFrom(Integer.class) || target.isAssignableFrom(Integer.TYPE))
            return rs.getInt(name);
        if (target.isAssignableFrom(Long.class) || target.isAssignableFrom(Long.TYPE))
            return rs.getLong(name);
        if (target.isAssignableFrom(Float.class) || target.isAssignableFrom(Float.TYPE))
            return rs.getFloat(name);
        if (target.isAssignableFrom(Double.class) || target.isAssignableFrom(Double.TYPE))
            return rs.getDouble(name);
        if (target.isAssignableFrom(String.class))
            return rs.getString(name);
        if (target.isAssignableFrom(Date.class))
            return rs.getDate(name);
        return null;
    }

    public void foreach(Callback<Object[]> callback, String sql, Object... args) throws SQLException {
        foreach(callback, 0, sql, args);
    }

    public void foreach(Callback<Object[]> callback, int len, String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(2000);

        if (statement instanceof com.mysql.jdbc.Statement)
            ((com.mysql.jdbc.Statement) statement).enableStreamingResults();

        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        while (rs.next()) {
            Object[] entity = new Object[columnCount + len];
            for (int i = 0; i < columnCount; i++)
                entity[i] = rs.getObject(i + 1);
            callback.call(entity);
        }

        close(rs, statement);
    }

    public void batchForeach(Callback<Object[][]> callback, int size, String sql, Object... args) throws SQLException {
        sql = getSQL(sql);
        PreparedStatement statement = getConnection().prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
        statement.setFetchSize(size);

        if (statement instanceof com.mysql.jdbc.Statement)
            ((com.mysql.jdbc.Statement) statement).enableStreamingResults();

        for (int i = 0; i < args.length; i++)
            statement.setObject(i + 1, args[i]);

        ResultSet rs = statement.executeQuery();
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        Object[][] result = new Object[size][];
        int row = 0;
        while (rs.next()) {
            Object[] entity = new Object[columnCount];
            for (int i = 0; i < columnCount; i++)
                entity[i] = rs.getObject(i + 1);
            result[row++] = entity;
            if (row >= size) {
                row = 0;
                callback.call(result);
            }
        }
        result[row] = null;
        callback.call(result);

        close(rs, statement);
    }

    public void begin() throws SQLException {
        getConnection().setAutoCommit(false);
        System.out.println("Begin Transactional");
    }

    public void commit() throws SQLException {
        getConnection().commit();
        getConnection().setAutoCommit(true);
        System.out.println("Commit Transactional");
    }

    public void rollback() {
        try {
            getConnection().rollback();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        try {
            getConnection().setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println("Rollback Transactional");
    }

    @Override
    public void close() {
        try {
            this.getConnection().close();
            System.out.println("Close Connection");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private void log(String sql, Object[] args, Object result) {
        if (showLog)
            System.out.println("SQL:[" + sql + "],Param[" + args + "],Result:[" + result + "]");
    }

    protected static void close(AutoCloseable... closeables) {
        if (closeables != null)
            for (AutoCloseable closeable : closeables) {
                try {
                    closeable.close();
                } catch (Exception e) {
                }
            }
    }

    public DalClient showLog(boolean showLog) {
        this.showLog = showLog;
        return this;
    }

    @FunctionalInterface
    public interface Callback<T> {
        boolean call(T entity) throws SQLException;
    }
}